/**
 * 
 */
package edu.gatech.oad.yuno;

import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.content.ContentValues;
import android.content.Context;


/**
 * @author michael
 *
 */
public class TodoListOpenHelper extends SQLiteOpenHelper {
	private static final int DATABASE_VERSION = 1;
    private static final String DATABASE_NAME = "YUNoDos";
    
    private static final String USER_TABLE_CREATE = 
    		"CREATE TABLE users (" +
    		"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    		"username TEXT UNIQUE NOT NULL, " + 
    		"password TEXT NOT NULL, " +
    		"email TEXT UNIQUE NOT NULL, " +
    		"name TEXT NOT NULL);";
    private static final String DEFAULT_USER_ADD =
    		"INSERT INTO users " +
    		"(username, password, email, name) " +
    		"VALUES ('default', 'default', 'default@example.com', " +
    		"'Default User');";
    private static final String TODO_TABLE_CREATE =
    		"CREATE TABLE todos (" +
    		"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    		"user_id INTEGER NOT NULL" +
    		" REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
    		"name TEXT NOT NULL, " +
    		"description TEXT, " +
    		"due_date INTEGER, " +
    		"completed INTEGER NOT NULL DEFAULT 0, " +
    		"recurring INTEGER NOT NULL DEFAULT 0, " +
    		"category_id INTEGER" +
    		" REFERENCES categories(id) ON DELETE SET NULL ON UPDATE CASCADE, " +
    		"location_id INTEGER" +
    		" REFERENCES locations(id) ON DELETE SET NULL ON UPDATE CASCADE, " +
    		"priority INTEGER NOT NULL DEFAULT 0);";
    private static final String SAMPLE_TODO_ADD =
    		"INSERT INTO todos " +
    		"(user_id, name, description, priority) " +
			"VALUES (1, 'Sample Todo', 'This is a sample todo.', 3);";
    private static final String CATEGORY_TABLE_CREATE = 
    		"CREATE TABLE categories (" +
    		"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    		"user_id INTEGER NOT NULL" +
    		" REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
    		"name TEXT NOT NULL, " +
    		"color TEXT);";
    private static final String CATEGORY_XREF_TABLE_CREATE =
    		"CREATE TABLE todos_categories (" +
    		"todo_id INTEGER NOT NULL" +
    		" REFERENCES todos(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
    		"category_id INTEGER NOT NULL" +
    		" REFERENCES categories(id) ON DELETE CASCADE ON UPDATE CASCADE);";
    private static final String LOCATION_TABLE_CREATE =
    		"CREATE TABLE locations (" +
    		"id INTEGER PRIMARY KEY AUTOINCREMENT, " +
    		"user_id INTEGER NOT NULL" +
    		" REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE, " +
    		"name TEXT NOT NULL, " +
    		"address TEXT, " +
    		"latitude TEXT, " +
    		"longitude TEXT);";

    TodoListOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
    	ContentValues values = new ContentValues();
        db.execSQL(USER_TABLE_CREATE);
        db.execSQL(DEFAULT_USER_ADD);
        db.execSQL(TODO_TABLE_CREATE);
        db.execSQL(SAMPLE_TODO_ADD);
        db.execSQL(CATEGORY_TABLE_CREATE);
        values.put("user_id", 0);
        values.put("name", "Personal");
        values.put("color", 0xffff0000);
        db.insert("categories", null, values);
        values.put("name", "School");
        values.put("color", 0xff00ff00);
        db.insert("categories", null, values);
        values.put("name", "Work");
        values.put("color", 0xff0000ff);
        db.insert("categories", null, values);
        //db.execSQL(CATEGORY_XREF_TABLE_CREATE);
        db.execSQL(LOCATION_TABLE_CREATE);
    }

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// TODO Auto-generated method stub

	}

}
